﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DALBase;
using OneMile.Entity;
using OneMile.Entity.Model;
using System.Collections;
using System.Data;
using OneMile.Common;

namespace OneMile.DAL
{
    public class DALUCoupon:BaseDAL
    {
        public DBAccess db = DBConnection.CreateDB();

        public IList<ECoupon> GetCouponListByUserIdAndShopId(string userId, string shopId, List<String> idList)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT * FROM Fct_Coupon ");
                sb.AppendLine(" WHERE Disabled = 0 AND IsUsed=0 AND UserId=@UserId AND ShopsId = @ShopsId ");
                sb.AppendLine("       AND CommodityId is NULL");
                sb.AppendLine("       AND (GETDATE() BETWEEN StartDate AND DATEADD(DAY,1,EndDate)) ");

                if (idList != null && idList.Count != 0)
                {
                    string commodityIds = "";
                    foreach (var id in idList)
                    {
                        commodityIds += String.Format("'{0}',", id);
                    }
                    commodityIds = commodityIds.Substring(0, commodityIds.Length - 1);

                    sb.AppendLine("   UNION ");
                    sb.AppendLine("SELECT * FROM Fct_Coupon ");
                    sb.AppendLine(" WHERE Disabled = 0 AND IsUsed=0 AND UserId=@UserId AND ShopsId = @ShopsId ");
                    sb.AppendLine("       AND (GETDATE() BETWEEN StartDate AND DATEADD(DAY,1,EndDate)) ");
                    sb.AppendLine(String.Format("AND (CommodityId IN ({0}))", commodityIds));
                }

                Hashtable hs = new Hashtable();
                hs.Add("UserId", userId);
                hs.Add("ShopsId", shopId);

                DataTable dt = db.ExecuteQuery(sb.ToString(), hs);
                IList<ECoupon> list = null;
                if (dt.Rows.Count > 0)
                {
                    list = new List<ECoupon>();
                    ECoupon coupon = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        coupon = new ECoupon();
                        Guid couponId = new Guid(dt.Rows[i]["CouponId"].TString());
                        Guid shopsId = new Guid(dt.Rows[i]["ShopsId"].TString());
                        coupon.CouponId = couponId;
                        coupon.ShopsId = shopsId;
                        coupon.CouponPrice = dt.Rows[i]["CouponPrice"].TDecimal();
                        coupon.CouponName = dt.Rows[i]["CouponName"].TString();
                        if(String.IsNullOrEmpty(dt.Rows[i]["CommodityId"].TString()))
                        {
                           coupon.CommodityId = Guid.Empty;
                        }
                        else
                        {
                           coupon.CommodityId = new Guid(dt.Rows[i]["CommodityId"].TString());
                        }

                        coupon.UserId = new Guid(dt.Rows[i]["UserId"].TString());
                        coupon.IsUsed = dt.Rows[i]["IsUsed"].TInt32();
                        coupon.StartDate = dt.Rows[i]["StartDate"].TDateTime();
                        coupon.EndDate = dt.Rows[i]["EndDate"].TDateTime();
                        coupon.UsedTime = dt.Rows[i]["UsedTime"].TDateTime();
                        list.Add(coupon);
                    }
                }

                return list;
            }
            catch (Exception e)
            {

                base.WriteLog(e.Message);
                throw e;
            }
        }

        public IList<ECoupon> GetCouponListByUserId(string userId,int pageNo, int pageSize)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT * FROM");
                sb.AppendLine(" ( ");
                sb.AppendLine("	  SELECT * , ROW_NUMBER() OVER(ORDER BY EndDate) as RowNum ");
                sb.AppendLine("   From Fct_Coupon");
                sb.AppendLine("	  WHERE UserId=@UserId AND (GETDATE() BETWEEN StartDate AND DATEADD(DAY,1,EndDate))"); 
                sb.AppendLine(" )as C");
                sb.AppendLine("WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize");

                Hashtable hs = new Hashtable();
                hs.Add("UserId", userId);
                hs.Add("PageNo", pageNo);
                hs.Add("PageSize", pageSize);

                DataTable dt = db.ExecuteQuery(sb.ToString(), hs);
                IList<ECoupon> list = null;
                if (dt.Rows.Count > 0)
                {
                    list = new List<ECoupon>();
                    ECoupon coupon = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        coupon = new ECoupon();
                        Guid couponId = new Guid(dt.Rows[i]["CouponId"].TString());
                        Guid shopId = new Guid(dt.Rows[i]["ShopsId"].TString());
                        coupon.CouponId = couponId;
                        coupon.ShopsId = shopId;
                        coupon.CouponPrice = dt.Rows[i]["CouponPrice"].TDecimal();
                        coupon.CouponName = dt.Rows[i]["CouponName"].TString();
                        coupon.CommodityId = dt.Rows[i]["CommodityId"].TGuid();
                        coupon.UserId = new Guid( dt.Rows[i]["UserId"].TString());
                        coupon.IsUsed = dt.Rows[i]["IsUsed"].TInt32();
                        coupon.StartDate = dt.Rows[i]["StartDate"].TDateTime();
                        coupon.EndDate = dt.Rows[i]["EndDate"].TDateTime();
                        coupon.UsedTime = dt.Rows[i]["UsedTime"].TDateTime();
                        list.Add(coupon);
                    }
                }

                return list;
            }
            catch (Exception e)
            {                
                base.WriteLog(e.Message);
                throw e;
            }
        }
    }
}
